﻿Imports System.Web
Imports System.Data
Imports Mysoft.Map.Data
Imports System.Data.SqlClient
Imports Mysoft.Map.Utility
Imports Mysoft.Map.Application.Security
Imports System.IO
Public Interface IMyDataAccess
    Function ExecProcedure(ByVal strProcedureName As String, ByRef Parameters() As SqlParameter, Optional ByVal Timeout As Integer = -1) As Integer
    Function GetDataItemString(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As String
    Function GetDataItemInt(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Integer
    Function ExecSQL(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Boolean
    Function GetDataTable(ByVal strSQL As String) As System.Data.DataTable
End Interface

Public Class MyDBDataAccess
    Implements IMyDataAccess
    '执行指定存储过程
    Public Function ExecProcedure(ByVal strProcedureName As String, ByRef Parameters() As SqlParameter, Optional ByVal Timeout As Integer = -1) As Integer Implements IMyDataAccess.ExecProcedure

        Dim MyConnection As SqlConnection = New SqlConnection(MyDB.GetSqlConnectionString())
        Try

            Dim MyCommand As New SqlCommand(strProcedureName, MyConnection)
            MyCommand.CommandType = CommandType.StoredProcedure

            For Each Parameter As SqlParameter In Parameters
                MyCommand.Parameters.Add(Parameter)
            Next

            MyCommand.Parameters.Add(New SqlParameter("ResultValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, Nothing))
            MyConnection.Open()
            If Timeout > 0 Then
                MyCommand.CommandTimeout = Timeout
            End If
            MyCommand.ExecuteNonQuery()

            Return MyCommand.Parameters("ResultValue").Value
        Catch ex As Exception
            Return -1
        Finally
            MyConnection.Close()
        End Try

    End Function
    Public Function GetDataItemString(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As String Implements IMyDataAccess.GetDataItemString
        Return MyDB.GetDataItemString(strSQL)
    End Function
    Public Function GetDataItemInt(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Integer Implements IMyDataAccess.GetDataItemInt
        Return MyDB.GetDataItemInt(strSQL)
    End Function
    Public Function ExecSQL(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Boolean Implements IMyDataAccess.ExecSQL
        Return MyDB.ExecSQL(strSQL)
    End Function
    Public Function GetDataTable(ByVal strSQL As String) As System.Data.DataTable Implements IMyDataAccess.GetDataTable
        Return MyDB.GetDataTable(strSQL)
    End Function
End Class

Public Class UnitTestDataAccess
    Implements IMyDataAccess

    Public Shared strConnection As String = "Data Source=MYS-TEST01\\SQL2000_DEV;Initial Catalog=dotnet_erp252;Persist Security Info=True;User ID=sa;Password=95938"
    '执行指定存储过程
    Public Function ExecProcedure(ByVal strProcedureName As String, ByRef Parameters() As SqlParameter, Optional ByVal Timeout As Integer = -1) As Integer Implements IMyDataAccess.ExecProcedure

        Dim MyConnection As SqlConnection = New SqlConnection(strConnection)
        Try

            Dim MyCommand As New SqlCommand(strProcedureName, MyConnection)
            MyCommand.CommandType = CommandType.StoredProcedure

            For Each Parameter As SqlParameter In Parameters
                MyCommand.Parameters.Add(Parameter)
            Next

            MyCommand.Parameters.Add(New SqlParameter("ResultValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, Nothing))
            MyConnection.Open()
            If Timeout > 0 Then
                MyCommand.CommandTimeout = Timeout
            End If
            MyCommand.ExecuteNonQuery()

            Return MyCommand.Parameters("ResultValue").Value
        Catch ex As Exception
            Return -1
        Finally
            MyConnection.Close()
        End Try

    End Function
    Public Function GetDataItemString(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As String Implements IMyDataAccess.GetDataItemString
        Dim MyConnection As SqlConnection = New SqlConnection(strConnection)
        Try
            Dim strReturn As String
            Dim MyCommand As New SqlCommand(strSQL, MyConnection)
            MyCommand.CommandType = CommandType.Text

            MyConnection.Open()
            If Timeout > 0 Then
                MyCommand.CommandTimeout = Timeout
            End If
            strReturn = MyCommand.ExecuteScalar().ToString()

            Return strReturn
        Catch ex As Exception
            Return ""
        Finally
            MyConnection.Close()
        End Try
    End Function
    Public Function GetDataItemInt(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Integer Implements IMyDataAccess.GetDataItemInt
        Dim MyConnection As SqlConnection = New SqlConnection(strConnection)
        Try
            Dim intReturn As Integer
            Dim MyCommand As New SqlCommand(strSQL, MyConnection)
            MyCommand.CommandType = CommandType.Text

            MyConnection.Open()
            If Timeout > 0 Then
                MyCommand.CommandTimeout = Timeout
            End If
            intReturn = CInt(Convert.ChangeType(MyCommand.ExecuteScalar(), GetType(Integer)))

            Return intReturn
        Catch ex As Exception
            Return -1
        Finally
            MyConnection.Close()
        End Try
    End Function
    Public Function ExecSQL(ByVal strSQL As String, Optional ByVal Timeout As Integer = -1) As Boolean Implements IMyDataAccess.ExecSQL
        Dim MyConnection As SqlConnection = New SqlConnection(strConnection)
        Try
            Dim intReturn As Integer
            Dim MyCommand As New SqlCommand(strSQL, MyConnection)
            MyCommand.CommandType = CommandType.Text

            MyConnection.Open()
            If Timeout > 0 Then
                MyCommand.CommandTimeout = Timeout
            End If
            intReturn = MyCommand.ExecuteNonQuery()

            Return True
        Catch ex As Exception
            Return False
        Finally
            MyConnection.Close()
        End Try
    End Function

    Public Function GetDataTable(ByVal strSQL As String) As System.Data.DataTable Implements IMyDataAccess.GetDataTable
        Dim ReturnDataTable As DataTable = New DataTable()
        Dim MyConnection As SqlConnection = New SqlConnection(strConnection)
        Try
            Dim MyCommand As New SqlCommand(strSQL, MyConnection)
            MyCommand.CommandType = CommandType.Text

            MyConnection.Open()

            MyCommand.CommandTimeout = 2000

            Dim MyDA As New SqlDataAdapter(MyCommand)
            MyDA.Fill(ReturnDataTable)

            Return ReturnDataTable
        Catch ex As Exception
            Return Nothing
        Finally
            MyConnection.Close()
        End Try
    End Function

End Class

Public Class MyDataAccessManage
    Public Shared Function GetDataAccess(ByVal b_bUnitTest As Boolean) As IMyDataAccess
        Dim MyDabaAccess1 As IMyDataAccess

        If b_bUnitTest Then
            MyDabaAccess1 = New UnitTestDataAccess()
        Else
            MyDabaAccess1 = New MyDBDataAccess()
        End If

        Return MyDabaAccess1

    End Function

End Class
